package com.example.myapplication1.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.myapplication1.ui.order.Order;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.HashMap;

public class DatabaseHelper extends SQLiteOpenHelper implements AutoCloseable {
    private static final String TAG = "DatabaseHelper";
    private static final String DATABASE_NAME = "PetCare.db";
    private static final int DATABASE_VERSION = 2;

    // 用户表
    public static final String TABLE_USERS = "users";
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_USERNAME = "username";
    public static final String COLUMN_PASSWORD = "password";
    public static final String COLUMN_PHONE = "phone";
    public static final String COLUMN_GENDER = "gender";
    public static final String COLUMN_BALANCE = "balance";
    private static final float INITIAL_BALANCE = 20.0f;

    // 创建用户表的SQL语句
    private static final String CREATE_TABLE_USERS = 
            "CREATE TABLE " + TABLE_USERS + "("
            + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + COLUMN_USERNAME + " TEXT UNIQUE NOT NULL, "
            + COLUMN_PASSWORD + " TEXT NOT NULL, "
            + COLUMN_PHONE + " TEXT NOT NULL, "
            + COLUMN_GENDER + " TEXT NOT NULL, "
            + COLUMN_BALANCE + " REAL DEFAULT " + INITIAL_BALANCE + ");";

    // 订单表
    public static final String TABLE_ORDERS = "orders";
    public static final String COLUMN_ORDER_ID = "order_id";
    public static final String COLUMN_USER_ID = "user_id";
    public static final String COLUMN_ORDER_TIME = "order_time";
    public static final String COLUMN_TOTAL_AMOUNT = "total_amount";
    public static final String COLUMN_ORDER_STATUS = "order_status";

    // 订单详情表
    public static final String TABLE_ORDER_ITEMS = "order_items";
    public static final String COLUMN_ITEM_ID = "item_id";
    public static final String COLUMN_PRODUCT_NAME = "product_name";
    public static final String COLUMN_QUANTITY = "quantity";
    public static final String COLUMN_PRICE = "price";

    // 用户商品表
    public static final String TABLE_USER_PRODUCTS = "user_products";
    public static final String COLUMN_PRODUCT_ID = "product_id";
    public static final String COLUMN_REMAINING_QUANTITY = "remaining_quantity";

    private SQLiteDatabase database;
    private final Context context;

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
        String dbPath = context.getDatabasePath(DATABASE_NAME).getAbsolutePath();
        Log.d(TAG, "Database Path: " + dbPath);
        Log.d(TAG, "Database exists: " + isDatabaseExists());
        
        // 确保数据库被创建
        try {
            database = getWritableDatabase();
            Log.d(TAG, "Successfully got writable database");
        } catch (Exception e) {
            Log.e(TAG, "Error initializing database: " + e.getMessage());
            e.printStackTrace();
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            Log.d(TAG, "Creating database and tables");
            
            // 创建用户表
            db.execSQL(CREATE_TABLE_USERS);
            Log.d(TAG, "Users table created successfully");
            
            // 添加测试账号
            ContentValues values = new ContentValues();
            values.put(COLUMN_USERNAME, "test");
            values.put(COLUMN_PASSWORD, "123456");
            values.put(COLUMN_PHONE, "13800138000");
            values.put(COLUMN_GENDER, "male");
            values.put(COLUMN_BALANCE, INITIAL_BALANCE);
            long id = db.insert(TABLE_USERS, null, values);
            Log.d(TAG, "Test account created with id: " + id);

            // 创建订单表
            String createOrdersTable = "CREATE TABLE " + TABLE_ORDERS + " (" +
                    COLUMN_ORDER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_USER_ID + " INTEGER, " +
                    COLUMN_ORDER_TIME + " DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    COLUMN_TOTAL_AMOUNT + " REAL, " +
                    COLUMN_ORDER_STATUS + " TEXT DEFAULT 'completed', " +
                    "location TEXT" +  // 添加位置字段
                    ")";
            db.execSQL(createOrdersTable);

            // 创建订单详情表
            String createOrderItemsTable = "CREATE TABLE " + TABLE_ORDER_ITEMS + " (" +
                    COLUMN_ITEM_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_ORDER_ID + " INTEGER, " +
                    COLUMN_PRODUCT_NAME + " TEXT, " +
                    COLUMN_QUANTITY + " INTEGER, " +
                    COLUMN_PRICE + " REAL, " +
                    "location TEXT, " +  // 添加位置字段
                    "FOREIGN KEY(" + COLUMN_ORDER_ID + ") REFERENCES " + TABLE_ORDERS + "(" + COLUMN_ORDER_ID + ")" +
                    ")";
            db.execSQL(createOrderItemsTable);

            // 创建用户商品表
            String createUserProductsTable = "CREATE TABLE " + TABLE_USER_PRODUCTS + " (" +
                    COLUMN_PRODUCT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_USER_ID + " INTEGER, " +
                    COLUMN_PRODUCT_NAME + " TEXT, " +
                    COLUMN_REMAINING_QUANTITY + " INTEGER" +
                    ")";
            db.execSQL(createUserProductsTable);

            // 创建图书馆猫咪表
            String createLibraryCatsTable = "CREATE TABLE library_cats (" +
                    "cat_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "cat_name TEXT NOT NULL, " +
                    "cat_image TEXT, " +
                    "description TEXT" +
                    ")";
            db.execSQL(createLibraryCatsTable);

            // 创建喂养记录表
            createFeedingRecordsTable(db);
            
            // 确保喂养记录表存在
            ensureFeedingRecordsTableExists(db);

            // 插入三只猫咪的基础数据
            String[] catNames = {"奶茶", "点点", "橘子"};
            String[] catDescriptions = {
                "图书馆的镇馆之宝，温柔优雅的英短小姐",
                "活泼好动的黑白相间小调皮",
                "暖男一枚，最爱晒太阳的橘猫"
            };
            String[] catImages = {
                "cat_naicha.jpg",
                "cat_diandian.jpg",
                "cat_juzi.jpg"
            };

            for (int i = 0; i < catNames.length; i++) {
                ContentValues catValues = new ContentValues();
                catValues.put("cat_name", catNames[i]);
                catValues.put("cat_image", catImages[i]);
                catValues.put("description", catDescriptions[i]);
                db.insert("library_cats", null, catValues);
            }

            // 插入人工湖猫咪数据
            String[] lakeCatNames = {"分粮", "优雅小橘", "干饭支队"};
            String[] lakeCatDescriptions = {
                "猫猫界也有孔融让梨",
                "优雅永不过时",
                "不会排队干饭的小猫不是好小猫"
            };
            String[] lakeCatImages = {
                "cat_feiliang.jpg",
                "cat_youyaxiaoju.jpg",
                "cat_ganfanzhidui.jpg"
            };

            // 创建人工湖猫咪表
            String createLakeCatsTable = "CREATE TABLE lake_cats (" +
                    "cat_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "cat_name TEXT NOT NULL, " +
                    "cat_image TEXT, " +
                    "description TEXT" +
                    ")";
            db.execSQL(createLakeCatsTable);

            // 插入人工湖猫咪数据
            for (int i = 0; i < lakeCatNames.length; i++) {
                ContentValues lakeCatValues = new ContentValues();
                lakeCatValues.put("cat_name", lakeCatNames[i]);
                lakeCatValues.put("cat_image", lakeCatImages[i]);
                lakeCatValues.put("description", lakeCatDescriptions[i]);
                db.insert("lake_cats", null, lakeCatValues);
            }

            // 创建食堂猫咪表
            String createCafeteriaCatsTable = "CREATE TABLE cafeteria_cats (" +
                    "cat_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "cat_name TEXT NOT NULL, " +
                    "cat_image TEXT, " +
                    "description TEXT" +
                    ")";
            db.execSQL(createCafeteriaCatsTable);

            // 插入食堂猫咪数据
            String[] cafeteriaCatNames = {"小馋猫", "美食家", "等待者"};
            String[] cafeteriaCatDescriptions = {
                "最爱在食堂门口等待美食的小可爱",
                "对美食有着极高品味的挑食猫",
                "总是安静等待食物的乖乖猫"
            };
            String[] cafeteriaCatImages = {
                "cat_xiaochanmao.jpg",
                "cat_meishijia.jpg",
                "cat_dengdaizhe.jpg"
            };

            for (int i = 0; i < cafeteriaCatNames.length; i++) {
                ContentValues cafeteriaCatValues = new ContentValues();
                cafeteriaCatValues.put("cat_name", cafeteriaCatNames[i]);
                cafeteriaCatValues.put("cat_image", cafeteriaCatImages[i]);
                cafeteriaCatValues.put("description", cafeteriaCatDescriptions[i]);
                db.insert("cafeteria_cats", null, cafeteriaCatValues);
            }

            // 创建教学楼猫咪表
            String createClassroomCatsTable = "CREATE TABLE classroom_cats (" +
                    "cat_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "cat_name TEXT NOT NULL, " +
                    "cat_image TEXT, " +
                    "description TEXT" +
                    ")";
            db.execSQL(createClassroomCatsTable);

            // 插入教学楼猫咪数据
            String[] classroomCatNames = {"学霸猫", "课堂监督", "午休专家"};
            String[] classroomCatDescriptions = {
                "最爱蹭课的好学猫咪",
                "总是认真听讲的乖学生",
                "午休时间的睡眠专家"
            };
            String[] classroomCatImages = {
                "cat_xuebamao.jpg",
                "cat_ketangjiandu.jpg",
                "cat_wuxiuzhuanjia.jpg"
            };

            for (int i = 0; i < classroomCatNames.length; i++) {
                ContentValues classroomCatValues = new ContentValues();
                classroomCatValues.put("cat_name", classroomCatNames[i]);
                classroomCatValues.put("cat_image", classroomCatImages[i]);
                classroomCatValues.put("description", classroomCatDescriptions[i]);
                db.insert("classroom_cats", null, classroomCatValues);
            }

            // 初始化田径场的猫咪数据
            initializeTrackFieldCats(db);

            Log.d(TAG, "All tables created successfully");
        } catch (Exception e) {
            Log.e(TAG, "Error creating database: " + e.getMessage());
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion);
        // 删除所有表
        db.execSQL("DROP TABLE IF EXISTS feeding_records");
        db.execSQL("DROP TABLE IF EXISTS track_field_cats");
        db.execSQL("DROP TABLE IF EXISTS lake_cats");
        db.execSQL("DROP TABLE IF EXISTS library_cats");
        db.execSQL("DROP TABLE IF EXISTS cafeteria_cats");
        db.execSQL("DROP TABLE IF EXISTS classroom_cats");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER_PRODUCTS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ORDER_ITEMS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ORDERS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        onCreate(db);
    }

    @Override
    public void close() {
        Log.d(TAG, "Closing database connection");
        if (database != null && database.isOpen()) {
            database.close();
        }
        super.close();
    }

    // 初始化用户饲料库存
    private void initializeUserFeedStock(long userId) {
        try {
            SQLiteDatabase db = getWritableDatabase();
            
            // 初始化三种饲料的库存
            String[] feedTypes = {"营养猫粮", "小猫冻干", "营养猫条"};
            int[] initialQuantities = {3, 2, 5};  // 初始库存数量
            
            for (int i = 0; i < feedTypes.length; i++) {
                ContentValues values = new ContentValues();
                values.put(COLUMN_USER_ID, userId);
                values.put(COLUMN_PRODUCT_NAME, feedTypes[i]);
                values.put(COLUMN_REMAINING_QUANTITY, initialQuantities[i]);
                
                db.insert(TABLE_USER_PRODUCTS, null, values);
            }
        } catch (Exception e) {
            Log.e(TAG, "Error initializing user feed stock: " + e.getMessage());
        }
    }

    // 添加新用户
    public long addUser(String username, String password, String phone, String gender) {
        try {
            database = getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put(COLUMN_USERNAME, username);
            values.put(COLUMN_PASSWORD, password);
            values.put(COLUMN_PHONE, phone);
            values.put(COLUMN_GENDER, gender);
            values.put(COLUMN_BALANCE, INITIAL_BALANCE);

            long id = database.insert(TABLE_USERS, null, values);
            if (id != -1) {
                // 初始化用户的饲料库存
                initializeUserFeedStock(id);
            }
            Log.d(TAG, "Added user with id: " + id);
            return id;
        } catch (Exception e) {
            Log.e(TAG, "Error adding user: " + e.getMessage());
            return -1;
        }
    }

    // 检查用户名是否已存在
    public boolean isUsernameExists(String username) {
        try {
            database = getReadableDatabase();
            Cursor cursor = database.query(TABLE_USERS, new String[]{COLUMN_ID},
                    COLUMN_USERNAME + "=?", new String[]{username},
                    null, null, null);
            boolean exists = cursor.getCount() > 0;
            cursor.close();
            Log.d(TAG, "Username " + username + " exists: " + exists);
            return exists;
        } catch (Exception e) {
            Log.e(TAG, "Error checking username: " + e.getMessage());
            return false;
        }
    }

    // 验证用户登录
    public boolean checkUser(String username, String password) {
        try {
            database = getReadableDatabase();
            Cursor cursor = database.query(TABLE_USERS, new String[]{COLUMN_ID},
                    COLUMN_USERNAME + "=? AND " + COLUMN_PASSWORD + "=?",
                    new String[]{username, password},
                    null, null, null);
            boolean exists = cursor.getCount() > 0;
            cursor.close();
            Log.d(TAG, "User login check: " + exists);
            return exists;
        } catch (Exception e) {
            Log.e(TAG, "Error checking user: " + e.getMessage());
            return false;
        }
    }

    // 获取数据库文件路径
    public String getDatabasePath() {
        return context.getDatabasePath(DATABASE_NAME).getAbsolutePath();
    }

    // 检查数据库是否存在
    public boolean isDatabaseExists() {
        boolean exists = context.getDatabasePath(DATABASE_NAME).exists();
        Log.d(TAG, "Checking if database exists: " + exists);
        return exists;
    }

    // 验证用户名和手机号是否匹配
    public boolean verifyUserPhone(String username, String phone) {
        try {
            database = getReadableDatabase();
            Cursor cursor = database.query(TABLE_USERS,
                    new String[]{COLUMN_ID},
                    COLUMN_USERNAME + "=? AND " + COLUMN_PHONE + "=?",
                    new String[]{username, phone},
                    null, null, null);
            boolean exists = cursor.getCount() > 0;
            cursor.close();
            Log.d(TAG, "User and phone verification: " + exists);
            return exists;
        } catch (Exception e) {
            Log.e(TAG, "Error verifying user phone: " + e.getMessage());
            return false;
        }
    }

    // 验证用户密码
    public boolean verifyPassword(long userId, String password) {
        SQLiteDatabase db = this.getReadableDatabase();
        String[] columns = {"password"};
        String selection = "id = ?";
        String[] selectionArgs = {String.valueOf(userId)};
        
        try (Cursor cursor = db.query("users", columns, selection, selectionArgs, null, null, null)) {
            if (cursor != null && cursor.moveToFirst()) {
                String storedPassword = cursor.getString(cursor.getColumnIndexOrThrow("password"));
                return password.equals(storedPassword);
            }
        }
        return false;
    }

    // 更新用户密码
    public boolean updatePassword(long userId, String newPassword) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("password", newPassword);
        
        String whereClause = "id = ?";
        String[] whereArgs = {String.valueOf(userId)};
        
        int rowsAffected = db.update("users", values, whereClause, whereArgs);
        return rowsAffected > 0;
    }

    // 获取用户手机号
    public String getUserPhone(String username) {
        try {
            database = getReadableDatabase();
            Cursor cursor = database.query(TABLE_USERS,
                    new String[]{COLUMN_PHONE},
                    COLUMN_USERNAME + "=?",
                    new String[]{username},
                    null, null, null);
            
            String phone = "";
            if (cursor.moveToFirst()) {
                phone = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PHONE));
            }
            cursor.close();
            return phone;
        } catch (Exception e) {
            Log.e(TAG, "Error getting user phone: " + e.getMessage());
            return "";
        }
    }

    // 删除用户
    public boolean deleteUser(String username) {
        try {
            database = getWritableDatabase();
            int rowsAffected = database.delete(TABLE_USERS,
                    COLUMN_USERNAME + "=?",
                    new String[]{username});
            Log.d(TAG, "Deleted user rows: " + rowsAffected);
            return rowsAffected > 0;
        } catch (Exception e) {
            Log.e(TAG, "Error deleting user: " + e.getMessage());
            return false;
        }
    }

    // 获取用户ID
    public long getUserId(String username) {
        try {
            database = getReadableDatabase();
            Cursor cursor = database.query(TABLE_USERS,
                    new String[]{COLUMN_ID},
                    COLUMN_USERNAME + "=?",
                    new String[]{username},
                    null, null, null);
            
            long userId = -1;
            if (cursor.moveToFirst()) {
                userId = cursor.getLong(cursor.getColumnIndexOrThrow(COLUMN_ID));
            }
            cursor.close();
            Log.d(TAG, "Got user ID: " + userId + " for username: " + username);
            return userId;
        } catch (Exception e) {
            Log.e(TAG, "Error getting user ID: " + e.getMessage());
            return -1;
        }
    }

    // 获取用户余额
    public double getUserBalance(long userId) {
        try {
            database = getReadableDatabase();
            Cursor cursor = database.query(TABLE_USERS,
                    new String[]{COLUMN_BALANCE},
                    COLUMN_ID + "=?",
                    new String[]{String.valueOf(userId)},
                    null, null, null);
            
            if (cursor != null && cursor.moveToFirst()) {
                double balance = cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_BALANCE));
                cursor.close();
                return balance;
            }
            if (cursor != null) {
                cursor.close();
            }
            return INITIAL_BALANCE;
        } catch (Exception e) {
            Log.e(TAG, "Error getting user balance: " + e.getMessage());
            return INITIAL_BALANCE;
        }
    }

    // 更新用户余额
    public boolean updateUserBalance(long userId, double newBalance) {
        try {
            database = getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put(COLUMN_BALANCE, newBalance);

            int rowsAffected = database.update(TABLE_USERS,
                    values,
                    COLUMN_ID + "=?",
                    new String[]{String.valueOf(userId)});
            
            return rowsAffected > 0;
        } catch (Exception e) {
            Log.e(TAG, "Error updating user balance: " + e.getMessage());
            return false;
        }
    }

    // 获取用户的所有订单
    public List<Order> getUserOrders(long userId) {
        List<Order> orders = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        try {
            // 查询用户的所有订单
            Cursor orderCursor = db.query(
                TABLE_ORDERS,
                new String[]{COLUMN_ORDER_ID, COLUMN_ORDER_TIME, COLUMN_TOTAL_AMOUNT},
                COLUMN_USER_ID + "=?",
                new String[]{String.valueOf(userId)},
                null,
                null,
                COLUMN_ORDER_TIME + " DESC"
            );

            while (orderCursor.moveToNext()) {
                long orderId = orderCursor.getLong(orderCursor.getColumnIndexOrThrow(COLUMN_ORDER_ID));
                String orderTime = orderCursor.getString(orderCursor.getColumnIndexOrThrow(COLUMN_ORDER_TIME));
                double totalAmount = orderCursor.getDouble(orderCursor.getColumnIndexOrThrow(COLUMN_TOTAL_AMOUNT));

                // 生成订单号（使用订单ID和时间戳）
                String orderNumber = String.format("PET%d%d", orderId, System.currentTimeMillis() % 10000);

                // 查询订单详情
                List<Order.OrderItem> items = getOrderItems(orderId);

                // 创建订单对象
                Order order = new Order(
                    orderNumber,
                    parseDateTime(orderTime),
                    items,
                    totalAmount
                );
                orders.add(order);
            }
            orderCursor.close();
        } catch (Exception e) {
            Log.e(TAG, "Error getting user orders: " + e.getMessage());
        }

        return orders;
    }

    // 获取订单详情
    private List<Order.OrderItem> getOrderItems(long orderId) {
        List<Order.OrderItem> items = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        try {
            Cursor itemCursor = db.query(
                TABLE_ORDER_ITEMS,
                new String[]{COLUMN_PRODUCT_NAME, COLUMN_QUANTITY, COLUMN_PRICE},
                COLUMN_ORDER_ID + "=?",
                new String[]{String.valueOf(orderId)},
                null,
                null,
                null
            );

            while (itemCursor.moveToNext()) {
                String productName = itemCursor.getString(itemCursor.getColumnIndexOrThrow(COLUMN_PRODUCT_NAME));
                int quantity = itemCursor.getInt(itemCursor.getColumnIndexOrThrow(COLUMN_QUANTITY));
                double price = itemCursor.getDouble(itemCursor.getColumnIndexOrThrow(COLUMN_PRICE));

                items.add(new Order.OrderItem(productName, quantity, price));
            }
            itemCursor.close();
        } catch (Exception e) {
            Log.e(TAG, "Error getting order items: " + e.getMessage());
        }

        return items;
    }

    // 解析数据库中的日期时间字符串
    private Date parseDateTime(String dateTime) {
        try {
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault());
            return format.parse(dateTime);
        } catch (ParseException e) {
            Log.e(TAG, "Error parsing date: " + e.getMessage());
            return new Date();
        }
    }

    // 获取用户信息（手机号和性别）
    public String[] getUserInfo(long userId) {
        SQLiteDatabase db = this.getReadableDatabase();
        String[] columns = {COLUMN_PHONE, COLUMN_GENDER};
        String selection = COLUMN_ID + " = ?";
        String[] selectionArgs = {String.valueOf(userId)};
        
        try (Cursor cursor = db.query(TABLE_USERS, columns, selection, selectionArgs, null, null, null)) {
            if (cursor != null && cursor.moveToFirst()) {
                String phone = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PHONE));
                String gender = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_GENDER));
                return new String[]{phone, gender};
            }
        } catch (Exception e) {
            Log.e(TAG, "Error getting user info: " + e.getMessage());
        }
        return null;
    }

    // 获取所有图书馆猫咪信息
    public List<LibraryCat> getAllLibraryCats() {
        List<LibraryCat> cats = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        try {
            Cursor cursor = db.query("library_cats", 
                new String[]{"cat_id", "cat_name", "cat_image", "description"},
                null, null, null, null, "cat_id ASC");

            while (cursor.moveToNext()) {
                LibraryCat cat = new LibraryCat(
                    cursor.getInt(cursor.getColumnIndexOrThrow("cat_id")),
                    cursor.getString(cursor.getColumnIndexOrThrow("cat_name")),
                    cursor.getString(cursor.getColumnIndexOrThrow("cat_image")),
                    cursor.getString(cursor.getColumnIndexOrThrow("description"))
                );
                cats.add(cat);
            }
            cursor.close();
        } catch (Exception e) {
            Log.e(TAG, "Error getting library cats: " + e.getMessage());
        }
        
        return cats;
    }

    // 获取用户的饲料库存
    public Map<String, Integer> getUserFeedStock(long userId) {
        Map<String, Integer> feedStock = new HashMap<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        try {
            Cursor cursor = db.query(TABLE_USER_PRODUCTS,
                new String[]{COLUMN_PRODUCT_NAME, COLUMN_REMAINING_QUANTITY},
                COLUMN_USER_ID + "=?",
                new String[]{String.valueOf(userId)},
                null, null, null);

            while (cursor.moveToNext()) {
                String productName = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_NAME));
                int quantity = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_REMAINING_QUANTITY));
                feedStock.put(productName, quantity);
            }
            cursor.close();
        } catch (Exception e) {
            Log.e(TAG, "Error getting user feed stock: " + e.getMessage());
        }
        
        return feedStock;
    }

    // 记录喂养操作
    public boolean recordFeeding(long userId, int catId, String feedType, int amount) {
        SQLiteDatabase db = this.getWritableDatabase();
        boolean success = false;
        
        Log.d(TAG, "recordFeeding - 开始记录喂养操作 - 用户ID: " + userId + 
                   ", 猫咪ID: " + catId + ", 饲料类型: " + feedType + ", 数量: " + amount);
        
        try {
            // 确保feeding_records表存在
            ensureFeedingRecordsTableExists(db);
            
            // 检查库存
            String query = COLUMN_USER_ID + "=? AND " + COLUMN_PRODUCT_NAME + "=?";
            String[] args = new String[]{String.valueOf(userId), feedType};
            
            Cursor cursor = db.query(TABLE_USER_PRODUCTS,
                new String[]{COLUMN_REMAINING_QUANTITY},
                query,
                args,
                null, null, null);
                
            if (cursor != null && cursor.moveToFirst()) {
                int currentStock = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_REMAINING_QUANTITY));
                cursor.close();
                
                Log.d(TAG, "recordFeeding - 当前库存 " + feedType + ": " + currentStock);
                
                if (currentStock >= amount) {
                    // 更新库存
                    int newStock = currentStock - amount;
                    ContentValues values = new ContentValues();
                    values.put(COLUMN_REMAINING_QUANTITY, newStock);
                    
                    int updateResult = db.update(TABLE_USER_PRODUCTS,
                        values,
                        COLUMN_USER_ID + "=? AND " + COLUMN_PRODUCT_NAME + "=?",
                        new String[]{String.valueOf(userId), feedType});
                    
                    if (updateResult > 0) {
                        // 添加喂养记录
                        ContentValues feedingValues = new ContentValues();
                        feedingValues.put("user_id", userId);
                        feedingValues.put("cat_id", catId);
                        feedingValues.put("feed_type", feedType);
                        feedingValues.put("feed_amount", amount);
                        feedingValues.put("cat_location", determineCatLocation(catId));
                        
                        // 创建订单记录
                        long orderId = createOrderForFeeding(db, userId, feedType, amount);
                        Log.d(TAG, "recordFeeding - 创建订单记录，ID: " + orderId);
                        
                        // 直接执行SQL插入语句，避免可能的列名不匹配问题
                        String insertSql = "INSERT INTO feeding_records (user_id, cat_id, feed_type, feed_amount, cat_location) " +
                                         "VALUES (?, ?, ?, ?, ?)";
                        
                        db.execSQL(insertSql, 
                                  new Object[]{userId, catId, feedType, amount, determineCatLocation(catId)});
                        
                        Log.d(TAG, "recordFeeding - 成功插入喂养记录");
                        
                        // 验证记录是否成功插入
                        Cursor verificationCursor = db.rawQuery(
                            "SELECT * FROM feeding_records WHERE user_id=? AND cat_id=? ORDER BY feed_time DESC LIMIT 1",
                            new String[]{String.valueOf(userId), String.valueOf(catId)});
                            
                        if (verificationCursor != null && verificationCursor.moveToFirst()) {
                            Log.d(TAG, "recordFeeding - 验证确认：喂养记录已成功插入");
                            verificationCursor.close();
                            success = true;
                        } else {
                            Log.e(TAG, "recordFeeding - 验证失败：无法找到新插入的喂养记录");
                            if (verificationCursor != null) {
                                verificationCursor.close();
                            }
                        }
                        
                        Log.d(TAG, "recordFeeding - 成功更新库存到: " + newStock);
                    } else {
                        Log.e(TAG, "recordFeeding - 更新库存失败");
                    }
                } else {
                    Log.d(TAG, "recordFeeding - 库存不足 - 需要: " + amount + ", 可用: " + currentStock);
                }
            } else {
                Log.d(TAG, "recordFeeding - 未找到库存记录");
                if (cursor != null) {
                    cursor.close();
                }
            }
        } catch (Exception e) {
            Log.e(TAG, "recordFeeding - 错误: " + e.getMessage());
            e.printStackTrace();
        }
        
        Log.d(TAG, "recordFeeding - 操作结果: " + (success ? "成功" : "失败"));
        return success;
    }
    
    // 确保feeding_records表存在
    private void ensureFeedingRecordsTableExists(SQLiteDatabase db) {
        try {
            Cursor cursor = db.rawQuery(
                "SELECT name FROM sqlite_master WHERE type='table' AND name='feeding_records'", 
                null);
                
            boolean tableExists = cursor != null && cursor.getCount() > 0;
            if (cursor != null) {
                cursor.close();
            }
            
            if (!tableExists) {
                Log.d(TAG, "ensureFeedingRecordsTableExists - 创建feeding_records表");
                createFeedingRecordsTable(db);
            } else {
                Log.d(TAG, "ensureFeedingRecordsTableExists - feeding_records表已存在");
            }
        } catch (Exception e) {
            Log.e(TAG, "ensureFeedingRecordsTableExists - 错误: " + e.getMessage());
            // 尝试创建表
            createFeedingRecordsTable(db);
        }
    }

    // 创建喂养相关的订单记录
    private long createOrderForFeeding(SQLiteDatabase db, long userId, String feedType, int amount) {
        try {
            // 计算总价（这里可以设置每种饲料的价格）
            double unitPrice = 0.2; // 假设每单位饲料价格为0.2元
            double totalAmount = amount * unitPrice;
            
            // 插入订单记录
            ContentValues orderValues = new ContentValues();
            orderValues.put(COLUMN_USER_ID, userId);
            orderValues.put(COLUMN_TOTAL_AMOUNT, totalAmount);
            orderValues.put(COLUMN_ORDER_STATUS, "completed");
            
            long orderId = db.insert(TABLE_ORDERS, null, orderValues);
            
            // 插入订单详情
            ContentValues itemValues = new ContentValues();
            itemValues.put(COLUMN_ORDER_ID, orderId);
            itemValues.put(COLUMN_PRODUCT_NAME, feedType);
            itemValues.put(COLUMN_QUANTITY, amount);
            itemValues.put(COLUMN_PRICE, unitPrice);
            
            db.insert(TABLE_ORDER_ITEMS, null, itemValues);
            
            return orderId;
        } catch (Exception e) {
            Log.e(TAG, "Error creating order for feeding: " + e.getMessage());
            return -1;
        }
    }
    
    // 检查并初始化用户饲料库存
    public void checkAndInitializeUserFeedStock(long userId) {
        try {
            SQLiteDatabase db = getReadableDatabase();
            Cursor cursor = db.query(TABLE_USER_PRODUCTS,
                new String[]{COLUMN_PRODUCT_NAME},
                COLUMN_USER_ID + "=?",
                new String[]{String.valueOf(userId)},
                null, null, null);
            
            boolean hasStock = cursor.getCount() > 0;
            cursor.close();
            
            if (!hasStock) {
                // 如果用户没有饲料库存记录，则初始化
                initializeUserFeedStock(userId);
            }
        } catch (Exception e) {
            Log.e(TAG, "Error checking user feed stock: " + e.getMessage());
        }
    }
    
    // 确定猫咪所在位置
    private String determineCatLocation(int catId) {
        // 使用与getCatRegionById相同的逻辑
        if (catId >= 1 && catId <= 10) return "图书馆";
        if (catId >= 11 && catId <= 20) return "食堂";
        if (catId >= 21 && catId <= 30) return "运动馆";
        if (catId >= 31 && catId <= 40) return "教学楼";
        if (catId >= 41 && catId <= 50) return "人工湖";
        if (catId >= 51 && catId <= 60) return "田径场";
        return "未知区域";
    }
    
    // 获取用户的猫咪喂养记录
    public List<FeedingRecord> getUserFeedingRecords(long userId) {
        List<FeedingRecord> records = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        
        try {
            Log.d(TAG, "getUserFeedingRecords - 开始查询用户喂养记录，用户ID: " + userId);
            
            // 检查表中是否有数据
            Cursor countCheck = db.rawQuery(
                "SELECT COUNT(*) FROM feeding_records WHERE user_id = ?", 
                new String[]{String.valueOf(userId)});
            int recordCount = 0;
            if (countCheck != null && countCheck.moveToFirst()) {
                recordCount = countCheck.getInt(0);
                countCheck.close();
            }
            
            Log.d(TAG, "getUserFeedingRecords - 找到" + recordCount + "条喂养记录");
            
            if (recordCount == 0) {
                return records;
            }
            
            // 直接使用简单查询获取所有记录
            Cursor cursor = db.query(
                "feeding_records",
                null,
                "user_id = ?",
                new String[]{String.valueOf(userId)},
                null,
                null,
                "feed_time DESC");
            
            while (cursor.moveToNext()) {
                try {
                    long recordId = cursor.getLong(cursor.getColumnIndexOrThrow("record_id"));
                    int catId = cursor.getInt(cursor.getColumnIndexOrThrow("cat_id"));
                    String catLocation = cursor.getString(cursor.getColumnIndexOrThrow("cat_location"));
                    String feedType = cursor.getString(cursor.getColumnIndexOrThrow("feed_type"));
                    int feedAmount = cursor.getInt(cursor.getColumnIndexOrThrow("feed_amount"));
                    String feedTime = cursor.getString(cursor.getColumnIndexOrThrow("feed_time"));
                    
                    FeedingRecord record = new FeedingRecord(
                        recordId, 
                        userId, 
                        catId, 
                        catLocation, // 直接使用数据库中存储的位置
                        feedType, 
                        feedAmount, 
                        parseDateTime(feedTime)
                    );
                    
                    records.add(record);
                } catch (Exception e) {
                    Log.e(TAG, "getUserFeedingRecords - 处理记录时出错: " + e.getMessage());
                }
            }
            
            cursor.close();
            Log.d(TAG, "getUserFeedingRecords - 总共找到" + records.size() + "条记录");
        } catch (Exception e) {
            Log.e(TAG, "getUserFeedingRecords - 查询记录时出错: " + e.getMessage());
        }
        
        return records;
    }
    
    // 根据猫咪ID和位置获取猫咪名称
    private String getCatNameById(int catId, String location) {
        Log.d(TAG, "getCatNameById - 获取猫咪名称，ID: " + catId + ", 位置: " + location);

        String tableName = null;
        String nameColumn = "cat_name";  // 默认字段名
        
        switch (location) {
            case "图书馆":
                tableName = "library_cats";
                break;
            case "人工湖":
                tableName = "lake_cats";
                break;
            case "田径场":
                tableName = "track_field_cats";
                nameColumn = "name";  // 田径场表使用不同的字段名
                break;
            case "食堂":
                tableName = "cafeteria_cats";
                break;
            case "教学楼":
                tableName = "classroom_cats";
                break;
        }

        if (tableName != null) {
            SQLiteDatabase db = this.getReadableDatabase();
            try {
                Cursor cursor = db.query(
                    tableName,
                    new String[]{nameColumn},
                    "cat_id = ?",
                    new String[]{String.valueOf(catId)},
                    null, null, null
                );

                if (cursor != null && cursor.moveToFirst()) {
                    String catName = cursor.getString(cursor.getColumnIndexOrThrow(nameColumn));
                    cursor.close();
                    Log.d(TAG, "getCatNameById - 找到猫咪名称: " + catName);
                    return catName;
                }

                if (cursor != null) {
                    cursor.close();
                }
            } catch (Exception e) {
                Log.e(TAG, "Error in getCatNameById: " + e.getMessage());
            }
        }

        Log.d(TAG, "getCatNameById - 未找到猫咪名称，返回默认名称");
        return location + "的猫咪";
    }

    // 喂养记录类
    public static class FeedingRecord {
        private long recordId;
        private long userId;
        private int catId;
        private String catRegion;
        private String feedType;
        private int feedAmount;
        private Date feedTime;
        
        public FeedingRecord(long recordId, long userId, int catId, String catRegion, String feedType, int feedAmount, Date feedTime) {
            this.recordId = recordId;
            this.userId = userId;
            this.catId = catId;
            this.catRegion = catRegion;
            this.feedType = feedType;
            this.feedAmount = feedAmount;
            this.feedTime = feedTime;
        }
        
        public long getRecordId() { return recordId; }
        public long getUserId() { return userId; }
        public int getCatId() { return catId; }
        public String getCatRegion() { return catRegion; }
        public String getFeedType() { return feedType; }
        public int getFeedAmount() { return feedAmount; }
        public Date getFeedTime() { return feedTime; }
    }

    // 图书馆猫咪数据类
    public static class LibraryCat {
        private int id;
        private String name;
        private String image;
        private String description;

        public LibraryCat(int id, String name, String image, String description) {
            this.id = id;
            this.name = name;
            this.image = image;
            this.description = description;
        }

        public int getId() { return id; }
        public String getName() { return name; }
        public String getImage() { return image; }
        public String getDescription() { return description; }
    }

    private void initializeTrackFieldCats(SQLiteDatabase db) {
        try {
            // 创建田径场猫咪表
            String createTrackFieldCatsTable = "CREATE TABLE track_field_cats (" +
                    "cat_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "name TEXT NOT NULL, " +
                    "description TEXT, " +
                    "image TEXT, " +
                    "location TEXT DEFAULT 'track_field'" +
                    ")";
            db.execSQL(createTrackFieldCatsTable);
            Log.d(TAG, "Track field cats table created successfully");

            // 添加田径场的猫咪数据
            ContentValues values = new ContentValues();
            
            // 斑斑
            values.put("name", "斑斑");
            values.put("description", "石台上一坐，校园小卫士上线");
            values.put("image", "cat_banban");  // 移除.jpg后缀
            db.insert("track_field_cats", null, values);
            values.clear();

            // 翘翘
            values.put("name", "翘翘");
            values.put("description", "这眼神！校园怕不是已经限制了它的自由");
            values.put("image", "cat_qiaoqiao");  // 移除.jpg后缀
            db.insert("track_field_cats", null, values);
            values.clear();

            // 睡睡
            values.put("name", "睡睡");
            values.put("description", "睡橘就像个毛茸茸的橘色汤圆，每天蜷起来就是睡谁也别想打扰它的美梦");
            values.put("image", "cat_shuishui");  // 移除.jpg后缀
            db.insert("track_field_cats", null, values);
            values.clear();

            Log.d(TAG, "Track field cats data initialized successfully");
        } catch (Exception e) {
            Log.e(TAG, "Error initializing track field cats: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 修改喂养记录表的创建语句
    private void createFeedingRecordsTable(SQLiteDatabase db) {
        Log.d(TAG, "createFeedingRecordsTable - 开始创建feeding_records表");
        try {
            String createFeedingRecordsTable = "CREATE TABLE IF NOT EXISTS feeding_records (" +
                    "record_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    "cat_id INTEGER, " +
                    "cat_location TEXT, " +  // 添加猫咪位置字段
                    "user_id INTEGER, " +
                    "feed_type TEXT, " +
                    "feed_amount INTEGER, " +
                    "feed_time DATETIME DEFAULT CURRENT_TIMESTAMP, " +
                    "FOREIGN KEY(user_id) REFERENCES users(id)" +
                    ")";
            db.execSQL(createFeedingRecordsTable);
            Log.d(TAG, "createFeedingRecordsTable - feeding_records表创建成功");
            
            // 检查表是否确实创建成功
            Cursor cursor = db.rawQuery(
                "SELECT name FROM sqlite_master WHERE type='table' AND name='feeding_records'", 
                null);
            boolean tableExists = cursor != null && cursor.getCount() > 0;
            if (cursor != null) {
                cursor.close();
            }
            Log.d(TAG, "createFeedingRecordsTable - 表创建后验证: " + (tableExists ? "存在" : "不存在"));
        } catch (Exception e) {
            Log.e(TAG, "createFeedingRecordsTable - 创建表时出错: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // 删除用户及其所有关联数据
    public boolean deleteUserAndRelatedData(long userId) {
        SQLiteDatabase db = this.getWritableDatabase();
        boolean success = false;
        
        // 开始事务
        db.beginTransaction();
        try {
            Log.d(TAG, "开始删除用户（ID: " + userId + "）及其关联数据");
            
            // 1. 删除用户的喂养记录
            int feedingRecordsDeleted = db.delete(
                "feeding_records", 
                COLUMN_USER_ID + "=?", 
                new String[]{String.valueOf(userId)});
            Log.d(TAG, "删除了 " + feedingRecordsDeleted + " 条喂养记录");
            
            // 2. 删除用户的订单项
            // 先获取用户的所有订单ID
            List<Long> orderIds = new ArrayList<>();
            Cursor orderCursor = db.query(
                TABLE_ORDERS, 
                new String[]{COLUMN_ORDER_ID}, 
                COLUMN_USER_ID + "=?", 
                new String[]{String.valueOf(userId)}, 
                null, null, null);
            
            while (orderCursor != null && orderCursor.moveToNext()) {
                orderIds.add(orderCursor.getLong(orderCursor.getColumnIndexOrThrow(COLUMN_ORDER_ID)));
            }
            if (orderCursor != null) {
                orderCursor.close();
            }
            
            // 删除用户订单的所有订单项
            int orderItemsDeleted = 0;
            for (Long orderId : orderIds) {
                int deleted = db.delete(
                    TABLE_ORDER_ITEMS, 
                    COLUMN_ORDER_ID + "=?", 
                    new String[]{String.valueOf(orderId)});
                orderItemsDeleted += deleted;
            }
            Log.d(TAG, "删除了 " + orderItemsDeleted + " 条订单项");
            
            // 3. 删除用户的所有订单
            int ordersDeleted = db.delete(
                TABLE_ORDERS, 
                COLUMN_USER_ID + "=?", 
                new String[]{String.valueOf(userId)});
            Log.d(TAG, "删除了 " + ordersDeleted + " 条订单");
            
            // 4. 删除用户的商品记录
            int userProductsDeleted = db.delete(
                TABLE_USER_PRODUCTS, 
                COLUMN_USER_ID + "=?", 
                new String[]{String.valueOf(userId)});
            Log.d(TAG, "删除了 " + userProductsDeleted + " 条用户商品记录");
            
            // 5. 获取用户名
            String username = "";
            Cursor userCursor = db.query(
                TABLE_USERS, 
                new String[]{COLUMN_USERNAME}, 
                COLUMN_ID + "=?", 
                new String[]{String.valueOf(userId)}, 
                null, null, null);
            
            if (userCursor != null && userCursor.moveToFirst()) {
                username = userCursor.getString(userCursor.getColumnIndexOrThrow(COLUMN_USERNAME));
                userCursor.close();
            }
            
            // 6. 删除用户本身
            int usersDeleted = db.delete(
                TABLE_USERS, 
                COLUMN_ID + "=?", 
                new String[]{String.valueOf(userId)});
            Log.d(TAG, "删除了用户: " + (usersDeleted > 0 ? "成功" : "失败"));
            
            // 如果用户删除成功，则提交事务
            if (usersDeleted > 0) {
                db.setTransactionSuccessful();
                success = true;
                Log.d(TAG, "用户 " + username + " (ID: " + userId + ") 及其所有关联数据已成功删除");
            } else {
                Log.e(TAG, "删除用户失败，事务回滚");
            }
        } catch (Exception e) {
            Log.e(TAG, "删除用户数据时出错: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // 结束事务
            db.endTransaction();
        }
        
        return success;
    }

    // 根据编号返回区域
    public static String getCatRegionById(int catId) {
        if (catId >= 1 && catId <= 10) return "图书馆";
        if (catId >= 11 && catId <= 20) return "食堂";
        if (catId >= 21 && catId <= 30) return "运动馆";
        if (catId >= 31 && catId <= 40) return "教学楼";
        if (catId >= 41 && catId <= 50) return "人工湖";
        if (catId >= 51 && catId <= 60) return "田径场";
        return "未知区域";
    }
} 